from tortoise import BaseDBAsyncClient


async def upgrade(db: BaseDBAsyncClient) -> str:
    return """
        -- 添加用户表的打卡相关字段
        ALTER TABLE `users` ADD COLUMN `is_today_checkin` BOOL NOT NULL DEFAULT 0 COMMENT '今日是否已打卡';
        ALTER TABLE `users` ADD COLUMN `total_checkin_days` INT NOT NULL DEFAULT 0 COMMENT '总打卡天数';
        ALTER TABLE `users` ADD COLUMN `continuous_checkin_days` INT NOT NULL DEFAULT 0 COMMENT '连续打卡天数';
        ALTER TABLE `users` ADD COLUMN `last_checkin_date` DATE COMMENT '最后打卡日期';
        
        -- 添加检测记录表的有效性字段
        ALTER TABLE `detection_records` ADD COLUMN `is_valid` BOOL NOT NULL DEFAULT 0 COMMENT '检测结果是否有效（包含完整的BASP分型、描述、建议）';
        
        -- 创建打卡日历表
        CREATE TABLE IF NOT EXISTS `checkin_calendar` (
            `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            `checkin_date` DATE NOT NULL COMMENT '打卡日期',
            `points_earned` INT NOT NULL COMMENT '获得积分',
            `continuous_days` INT NOT NULL COMMENT '当日连续打卡天数',
            `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT '创建时间',
            `user_id` INT NOT NULL COMMENT '关联用户',
            CONSTRAINT `fk_checkin_calendar_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
            UNIQUE KEY `unique_user_date` (`user_id`, `checkin_date`)
        ) CHARACTER SET utf8mb4 COMMENT='打卡日历表';
        
        -- 创建索引
        CREATE INDEX `idx_checkin_calendar_date` ON `checkin_calendar` (`checkin_date`);
        CREATE INDEX `idx_checkin_calendar_user_date` ON `checkin_calendar` (`user_id`, `checkin_date`);
    """


async def downgrade(db: BaseDBAsyncClient) -> str:
    return """
        -- 删除打卡日历表
        DROP TABLE IF EXISTS `checkin_calendar`;
        
        -- 删除用户表的打卡相关字段
        ALTER TABLE `users` DROP COLUMN `is_today_checkin`;
        ALTER TABLE `users` DROP COLUMN `total_checkin_days`;
        ALTER TABLE `users` DROP COLUMN `continuous_checkin_days`;
        ALTER TABLE `users` DROP COLUMN `last_checkin_date`;
        
        -- 删除检测记录表的有效性字段
        ALTER TABLE `detection_records` DROP COLUMN `is_valid`;
    """